<?php

/**
 * SigmaCMS - Content Management System
 *
 * Copyright (C) 2008 Pavol Biely
 *
 * This program is free software: you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation, either version 3 of the License, or
 * (at your option) any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with this program. If not, see <http://www.gnu.org/licenses/>.
 *
 * @package    SigmaCMS
 * @author     Pavol Biely <pavolbiely@gmail.com>
 * @copyright  2008 Pavol Biely
 * @license    http://www.gnu.org/licenses/gpl.txt   GNU General Public License
 * @link       http://pabi3.com/
 * @since      0.1.0
 */

final class db extends cfg
{
	/**
	 * Supported databases
	 *
	 */
	const DB_TYPES = 'mysql,mysqli,mssql,fbsql,sqlite,postgresql';

	/**
	 * Database type
	 * 
	 * @var string
	 */
	static private $_type;

	/**
	 * Hostname of database server
	 * 
	 * @var string
	 */
	static private $_host;

	/**
	 * Virtual port
	 * 
	 * @var integer
	 */
	static private $_port;

	/**
	 * Database name
	 * 
	 * @var string
	 */
	static private $_database;

	/**
	 * Prefix for tables
	 * 
	 * @var string
	 */
	static private $_prefix;

	/**
	 * Username
	 * 
	 * @var string
	 */
	static private $_username;

	/**
	 * Password
	 * 
	 * @var string
	 */
	static private $_password;

	/**
	 * Persistent connection?
	 * 
	 * @var boolean
	 */
	static private $_persistent;

	/**
	 * Connection link
	 * 
	 * @var object
	 */
	static private $_connection;

	/**
	 * PostgreSQL query result for later use when calling db::lastId()
	 * 
	 * @var object
	 */
	static private $_lastOid;

	/**
	 * Used to remember parameters of special queries
	 *
	 * @var unknown_type
	 */
	static private $_sQuery = NULL;

	/**
	 * Number of an executed SQL queries
	 * 
	 * @var integer
	 */
	static public $queries = 0;

	/**
	 * Initialization
	 * 
	 * @return void
	 */
	static public function initialize()
	{
		// already connected?
		if (self::$_connection) return;

		// load access configuration
		self::$_host       = (string)  parent::$_dbHost;
		self::$_port       = (int)     parent::$_dbPort;
		self::$_type       = (string)  parent::$_dbType;
		self::$_database   = (string)  parent::$_dbDatabase;
		self::$_prefix     = (string)  parent::$_dbPrefix;
		self::$_password   = (string)  parent::$_dbPassword;
		self::$_username   = (string)  parent::$_dbUsername;
		self::$_persistent = (boolean) parent::$_dbPersistent;

		// database type not supported
		if (empty(self::$_type) || !stristr(self::DB_TYPES,self::$_type)) {
		    throw new Sigma_Exception('Invalid database type selected. It must be one of these: '.self::DB_TYPES);
		}

		// open a new connection
		self::$_connection = self::connectToDatabase();
		self::selectDatabase();
		self::setCharset();
	}

	/**
	 * Close the connection
	 * 
	 * @return void
	 */
	static public function deInitialize()
	{
		switch (self::$_type) {
			case 'mysql':
				@mysql_close(self::$_connection);
				break;

			case 'mysqli':
				@mysqli_close(self::$_connection);
				break;

			case 'mssql':
				@mssql_close(self::$_connection);
				break;

			case 'fbsql':
				@fbsql_close(self::$_connection);
				break;

			case 'sqlite':
				@sqlite_close(self::$_connection);
				break;

			case 'postgresql':
				@pg_close(self::$_connection);
				break;
		}

		self::$_host       = NULL;
		self::$_port       = NULL;
		self::$_type       = NULL;
		self::$_database   = NULL;
		self::$_prefix     = NULL;
		self::$_password   = NULL;
		self::$_username   = NULL;
		self::$_connection = NULL;
	}

	/**
	 * Currently used database type
	 * 
	 * @return string
	 */
	static public function getType()
	{
		return self::$_type;
	}

	/**
	 * Set the client character set to UTF-8
	 * 
	 * @return void
	 */
	static private function setCharset()
	{
		switch (self::$_type) {
			case 'mysql':
				// from PHP 5.2.3
				if (function_exists('mysql_set_charset')) {
					mysql_set_charset('utf8', self::$_connection);
				} else {
					self::query("SET NAMES utf8");
				}
				break;

			case 'mysqli':
				mysqli_set_charset(self::$_connection, 'utf8');
				break;

			case 'mssql':
				ini_set('mssql.charset','UTF-8');
				break;

			case 'fbsql':
				fbsql_set_characterset(self::$_connection, 'utf8');
				break;

			case 'sqlite':
				// not required
				break;

			case 'postgresql':
				pg_set_client_encoding(self::$_connection, UNICODE);
				break;
		}
	}

	/**
	 * Open a connection to a SQL server
	 * 
	 * @return mixed
	 */
	static private function connectToDatabase()
	{
		$connect = false;

		switch (self::$_type) {
			case 'mysql':
				if (self::$_persistent) {
					$connect = @mysql_pconnect(self::$_host.(!empty(self::$_port) ? ':'.self::$_port : NULL),self::$_username,self::$_password);
				} else {
					$connect = @mysql_connect(self::$_host.(!empty(self::$_port) ? ':'.self::$_port : NULL),self::$_username,self::$_password);
				}
				if (!$connect) {
					header('HTTP/1.1 503 Service Unavailable');
					throw new Sigma_Exception('Database Connection Failed (MySQL)');
				}
				break;

			case 'mysqli':
				// mysqli does not support persistent connection, so only classic way is available
				$connect = @mysqli_connect(self::$_host,self::$_username,self::$_password,self::$_database,self::$_port);
				if (!$connect) {
					header('HTTP/1.1 503 Service Unavailable');
					throw new Sigma_Exception('Database Connection Failed (MySQLi)');
				}
				break;

			case 'mssql':
				// include assistant functions for MSSQL
				require_once dirname(__FILE__).'/db/lib.mssql.php';

				if (self::$_persistent) {
					$connect = @mssql_pconnect(self::$_host,self::$_username,self::$_password);
				} else {
					$connect = @mssql_connect(self::$_host,self::$_username,self::$_password);
				}
				if (!$connect) {
					header('HTTP/1.1 503 Service Unavailable');
					throw new Sigma_Exception('Database Connection Failed (Microsoft SQL Server)');
				}

				// add new features to MSSQL
				DB_ExtendMssql::initialize($connect);
				break;

			case 'fbsql':
				// include assistant functions for FrontBase
				require_once dirname(__FILE__).'/db/lib.fbsql.php';

				if (self::$_persistent) {
					$connect = @fbsql_pconnect(self::$_host,self::$_username,self::$_password);
				} else {
					$connect = @fbsql_connect(self::$_host,self::$_username,self::$_password);
				}
				if (!$connect) {
					header('HTTP/1.1 503 Service Unavailable');
					throw new Sigma_Exception('Database Connection Failed (FrontBase)');
				}

				// add new features to FrontBase
				DB_ExtendFbsql::initialize($connect);
				break;

			case 'sqlite':
				// include assistant functions for SQLite
				require_once dirname(__FILE__).'/db/lib.sqlite.php';

				// database file path
				$filename = dirname(__FILE__).'/../'.self::$_database;
				if (self::$_persistent) {
					$connect = @sqlite_popen($filename, 0666);
				} else {
					$connect = @sqlite_open($filename, 0666);
				}
				if (!$connect) {
					header('HTTP/1.1 503 Service Unavailable');
					throw new Sigma_Exception('Database Connection Failed (SQLite)');
				}

				// add new features to SQLite
				DB_ExtendSqlite::initialize($connect);
				break;

			case 'postgresql':
				// include assistant functions for PostgreSQL
				require_once dirname(__FILE__).'/db/lib.postgresql.php';

				$connectionString = "host=".self::$_host." port=".self::$_port." dbname=".self::$_database." user=".self::$_username." password=".self::$_password;
				if (self::$_persistent) {
					$connect = @pg_pconnect($connectionString);
				} else {
					$connect = @pg_connect($connectionString);
				}
				if (!$connect) {
					header('HTTP/1.1 503 Service Unavailable');
					throw new Sigma_Exception('Database Connection Failed (PostgreSQL)');
				}

				// add new features to PostgreSQL
				DB_ExtendPostgresql::initialize($connect);
				break;
		}

		return $connect;
	}

	/**
	 * Select a SQL database
	 * 
	 * @return void
	 */
	static private function selectDatabase()
	{
		switch (self::$_type) {
			case 'mysql':
				$select = @mysql_select_db(self::$_database,self::$_connection);
				if (!$select) {
					header('HTTP/1.1 503 Service Unavailable');
					throw new Sigma_Exception('Database Selection Failed (MySQL)');
				}
				break;

			case 'mysqli':
				$select = @mysqli_select_db(self::$_connection,self::$_database);
				if (!$select) {
					header('HTTP/1.1 503 Service Unavailable');
					throw new Sigma_Exception('Database Selection Failed (MySQLi)');
				}
				break;

			case 'mssql':
				$select = @mssql_select_db(self::$_database,self::$_connection);
				if (!$select) {
					header('HTTP/1.1 503 Service Unavailable');
					throw new Sigma_Exception('Database Selection Failed (Microsoft SQL Server)');
				}
				break;

			case 'fbsql':
				$select = @fbsql_select_db(self::$_database,self::$_connection);
				if (!$select) {
					header('HTTP/1.1 503 Service Unavailable');
					throw new Sigma_Exception('Database Selection Failed (FrontBase)');
				}
				break;

			case 'sqlite':
			case 'postgresql':
				// not required
				break;
		}
	}

	/**
	 * Check if the system is connected to the database server
	 *
	 * @return boolean
	 */
	static public function isConnected()
	{
		if (self::$_connection) {
			return true;
		} else {
			return false;
		}
	}

	/**
	 * Add a prefix to table
	 *
	 * @param string $table
	 * @return string
	 */
	static public function prefix($table)
	{
		if (preg_match('/^(\w+)$/', $table)) {
			return self::$_prefix.$table;
		} else {
			return $table;
		}
	}

	/**
	 * Secure query
	 *
	 * @param array $matches
	 * @return callback
	 */
	static public function secureQuery($matches)
	{
		switch ($matches[1]) {
			case 'i':
				return (integer) self::$_sQuery;
			case 'f':
				return (float)   self::$_sQuery;
			case 's':
				return (string)  self::escape(self::$_sQuery);
			case 'b':
				return (boolean) self::$_sQuery;
			case 't':
				return (string)  self::prefix(self::$_sQuery);
			default:
				return NULL;
		}
	}

	/**
	 * Send a SQL query
	 *
	 * @param string $sql
	 * @return mixed
	 */
	static public function query($sql)
	{
		// query result
		$result = false;

		// additional parameters
		$params = func_get_args();

		// special query
		if (count($params) > 1) {
			$query = array();
			for ($i = 0; $i < count($params); $i++) {
				if ($i%2 == 0) {
					self::$_sQuery = $params[$i+1];
					$params[$i] = preg_replace_callback('/%(i|f|s|b|t)/', array('self', 'secureQuery'), $params[$i]);
					$query[] = $params[$i];
				}
			}
			$sql = implode(" ", $query);
		}

		// choose database type
		switch (self::$_type) {
			case 'mysql':
				$result = @mysql_query($sql,self::$_connection);
				break;

			case 'mysqli':
				$result = @mysqli_query(self::$_connection,$sql);
				break;

			case 'mssql':
				DB_ExtendMssql::cleanQuery($sql);
				$result = @mssql_query($sql,self::$_connection);
				break;

			case 'fbsql':
				DB_ExtendFbsql::cleanQuery($sql);
				$result = @fbsql_query($sql,self::$_connection);
				break;

			case 'sqlite':
				DB_ExtendSqlite::cleanQuery($sql);
				$result = @sqlite_query(self::$_connection,$sql);
				break;

			case 'postgresql':
				DB_ExtendPostgresql::cleanQuery($sql);
				$result = @pg_query(self::$_connection,$sql);
				self::$_lastOid = $result;
				break;
		}

		// no error occurred?
		if ($result) {
			// increase the SQL query counter
			self::$queries++;
		} else {
			// print the SQL query
			echo _hsc($sql, ENT_NOQUOTES);
		}

		return $result;
	}

	/**
	 * Execute a SQL query and return the first result row
	 * 
	 * @param string $query
	 * @return mixed
	 */
	static public function fQuery($query)
	{
		$sql = self::query($query);
		$row = self::fetchArray($sql);
		if (self::numRows($sql) > 0) {
			return $row[0];
		}
		return false;
	}

	/**
	 * Fetch a result row as an object
	 * 
	 * @param object $resource
	 * @return object
	 */
	static public function fetchObject($resource)
	{
		switch (self::$_type) {
			case 'mysql':
				return @mysql_fetch_object($resource);

			case 'mysqli':
				return @mysqli_fetch_object($resource);

			case 'mssql':
				return @mssql_fetch_object($resource);

			case 'fbsql':
				return @fbsql_fetch_object($resource);

			case 'sqlite':
				return @sqlite_fetch_object($resource);

			case 'postgresql':
				return @pg_fetch_object($resource);
		}
	}

	/**
	 * Fetch a result row as an associative array, a numeric array, or both
	 * 
	 * @param object $resource
	 * @return array
	 */
	static public function fetchArray($resource)
	{
		switch (self::$_type) {
			case 'mysql':
				return @mysql_fetch_array($resource, MYSQL_BOTH);

			case 'mysqli':
				return @mysqli_fetch_array($resource, MYSQL_BOTH);

			case 'mssql':
				return @mssql_fetch_array($resource, MSSQL_BOTH);

			case 'fbsql':
				return @fbsql_fetch_array($resource, FBSQL_BOTH);

			case 'sqlite':
				return @sqlite_fetch_array($resource, SQLITE_BOTH);

			case 'postgresql':
				return @pg_fetch_array($resource, NULL, PGSQL_BOTH);
		}
	}

	/**
	 * Fetch a result row as an associative array
	 * 
	 * @param object $resource
	 * @return array
	 */
	static public function fetchAssoc($resource)
	{
		switch (self::$_type) {
			case 'mysql':
				return @mysql_fetch_assoc($resource);

			case 'mysqli':
				return @mysqli_fetch_assoc($resource);

			case 'mssql':
				return @mssql_fetch_assoc($resource);

			case 'fbsql':
				return @fbsql_fetch_assoc($resource);

			case 'sqlite':
				return @sqlite_fetch_array($resource, SQLITE_ASSOC);

			case 'postgresql':
				return @pg_fetch_assoc($resource, NULL, PGSQL_ASSOC);
		}
	}

	/**
	 * Get a result row as an enumerated array
	 * 
	 * @param object $resource
	 * @return array
	 */
	static public function fetchRow($resource)
	{
		switch (self::$_type) {
			case 'mysql':
				return @mysql_fetch_row($resource);

			case 'mysqli':
				return @mysqli_fetch_row($resource);

			case 'mssql':
				return @mssql_fetch_row($resource);

			case 'fbsql':
				return @fbsql_fetch_row($resource);

			case 'sqlite':
				return @sqlite_fetch_array($resource, SQLITE_NUM);

			case 'postgresql':
				return @pg_fetch_row($resource);
		}
	}

	/**
	 * Escapes special characters in a string for use in a SQL statement
	 * 
	 * @param string $input
	 * @return string
	 */
	static public function escape($input)
	{
		switch (self::$_type) {
			case 'mysql':
				return @mysql_real_escape_string($input, self::$_connection);

			case 'mysqli':
				return @mysqli_real_escape_string(self::$_connection, $input);

			case 'mssql':
				return str_replace("'", "''", $input);

			case 'fbsql':
				return str_replace("'", "''", $input); // ?

			case 'sqlite':
				return @sqlite_escape_string($input);

			case 'postgresql':
				return @pg_escape_string($input);
		}
	}

	/**
	 * Get number of rows in result
	 *
	 * @param object $resource
	 * @return integer
	 */
	static public function numRows($resource)
	{
		switch (self::$_type) {
			case 'mysql':
				return @mysql_num_rows($resource);

			case 'mysqli':
				return @mysqli_num_rows($resource);

			case 'mysqli':
				return @mssql_num_rows($resource);

			case 'fbsql':
				return @fbsql_num_rows($resource);

			case 'sqlite':
				return @sqlite_num_rows($resource);

			case 'postgresql':
				return @pg_num_rows($resource);
		}
	}

	/**
	 * Get number of fields in result
	 * 
	 * @param object $resource
	 * @return integer
	 */
	static public function numFields($resource)
	{
		switch (self::$_type) {
			case 'mysql':
				return @mysql_num_fields($resource);

			case 'mysqli':
				return @mysqli_num_fields($resource);

			case 'mssql':
				return @mssql_num_fields($resource);

			case 'fbsql':
				return @fbsql_num_fields($resource);

			case 'sqlite':
				return @sqlite_num_fields($resource);

			case 'postgresql':
				return @pg_num_fields($resource);
		}
	}

	/**
	 * Get the ID generated from the previous INSERT operation
	 * 
	 * @param string $table
	 * @return mixed
	 */
	static public function lastId($table = NULL)
	{
		switch (self::$_type) {
			case 'mysql':
				return @mysql_insert_id(self::$_connection);

			case 'mysqli':
				return @mysqli_insert_id(self::$_connection);

			case 'mssql':
				$result = @mssql_query("SELECT @@identity", self::$_connection);
				if (!$result) {
					return -1;
				}
				return @mssql_result($result, 0, 0);

			case 'fbsql':
				return @fbsql_insert_id(self::$_connection);

			case 'sqlite':
				return @sqlite_last_insert_rowid(self::$_connection);

			case 'postgresql':
				return @pg_last_oid(self::$_lastOid);
		}
	}

	/**
	 * Free result memory
	 * 
	 * @param object $resource
	 * @return void
	 */
	static public function free($resource)
	{
		switch (self::$_type) {
			case 'mysql':
				@mysql_free_result($resource);
				break;

			case 'mysqli':
				@mysqli_free_result($resource);
				break;

			case 'mssql':
				@mssql_free_result($resource);
				break;

			case 'fbsql':
				@fbsql_free_result($resource);
				break;

			case 'sqlite':
				unset($resource);
				break;

			case 'postgresql':
				@pg_free_result($resource);
				break;
		}
	}

	/**
	 * Returns the text of the error message from previous SQL operation
	 *
	 * @return string
	 */
	static public function error()
	{
		switch (self::$_type) {
			case 'mysql':
				return mysql_error(self::$_connection);

			case 'mysqli':
				return mysqli_error(self::$_connection);

			case 'mssql':
				return mssql_get_last_message();

			case 'fbsql':
				return fbsql_error(self::$_connection);

			case 'sqlite':
				return sqlite_error_string(sqlite_last_error(self::$_connection));

			case 'postgresql':
				return pg_last_error(self::$_connection);
		}
	}
}
